package com.blog.dazi.Service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.blog.dazi.utils.RandomDataGenerator;
import com.blog.dazi.utils.TravelInfoGenerator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import java.awt.font.ImageGraphicAttribute;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.Map;
import java.util.Random;

@Service
public class ContentService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public List<Map<String, Object>> list(int pageNum, int pageSize) {
        String sql = "SELECT " +
                "  a.content,a.good, " +
                "  a.id, " +
                "  b.username, " +
                "  b.id userId, " +
                "  c.photo, " +
                "  d.photo userPhoto " +
                "FROM " +
                "  (select * from sys_user_want_to ORDER BY id DESC " +
                "LIMIT ?, " +
                " ?) a, " +
                "  sys_user b, " +
                "  sys_content_photo c, " +
                "  sys_user_photo d " +
                "WHERE " +
                "  a.user_id = b.id " +
                "AND a.id = c.want_to_id " +
                "AND b.photo_id = d.id ";
        int index = (pageNum-1)*pageSize;
        return jdbcTemplate.queryForList(sql,index,pageSize);
    }

    public List<Map<String, Object>> findByDazi(String daziNum, int pageNum, int pageSize) {
        String sql = "SELECT " +
                "  a.content, " +
                "  a.id,a.good, " +
                "  b.username, " +
                "  b.id userId, " +
                "  c.photo, " +
                "  d.photo userPhoto " +
                "FROM " +
                "  sys_user_want_to a, " +
                "  sys_user b, " +
                "  sys_content_photo c, " +
                "  sys_user_photo d " +
                "WHERE " +
                "  a.user_id = b.id " +
                "AND a.id = c.want_to_id " +
                "AND b.photo_id = d.id and b.dazi_number = ? ORDER BY a.id DESC LIMIT ?, ?";
        int index = (pageNum-1)*pageSize;
        return jdbcTemplate.queryForList(sql,daziNum,index,pageSize);
    }

    public List<Map<String, Object>> findNearbyByTel(String tel, int pageNum, int pageSize) {
        String sql = "select b.longitude,b.latitude from sys_user a,bd_location b where a.phone_number = ? and a.localtion_id = b.id";
        Map<String, Object> map = jdbcTemplate.queryForMap(sql, tel);
        String sql1 = "SELECT a.id ,a.user_id,a.content,a.distance FROM (SELECT *, TRUNCATE (st_distance_sphere (point (?,?),point (publish_location_longitude,publish_location_latitude " +
                ")),2) AS distance FROM sys_user_want_to ORDER By distance) a WHERE a.distance <= 5000 limit ?,?";
        try {
            int index = (pageNum-1)*pageSize;
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql1, map.get("longitude"), map.get("latitude"),index,pageSize);
            if(maps!=null && maps.size()>0){
                StringBuffer ids = new StringBuffer();
                ids.append("(");
                StringBuffer userId = new StringBuffer();
                userId.append("(");
                maps.forEach(m->{
                    Integer id = (Integer) m.get("id");
                    ids.append(id);
                    ids.append(",");

                    Integer user_id = (Integer) m.get("user_id");
                    userId.append(user_id);
                    userId.append(",");
                });

                String idss = ids.substring(0, ids.length() - 1);
                idss +=")";
                String sql2 = "select * from sys_content_photo where want_to_id in "+idss;
                List<Map<String, Object>> maps1 = jdbcTemplate.queryForList(sql2);

                String userIds = userId.substring(0, userId.length() - 1);
                userIds +=")";
                String sql3 = "select a.id, a.username,b.photo,a.sex from sys_user a,sys_user_photo b where a.photo_id = b.id and a.id in  "+userIds;
                List<Map<String, Object>> users = jdbcTemplate.queryForList(sql3);

                maps.forEach(m->{
                    maps1.forEach(o->{
                        if(((Integer)m.get("id")).equals ((Integer) o.get("want_to_id"))){
                            m.put("photo",o.get("photo"));
                        }
                    });

                    users.forEach(o->{
                        if(((Integer)m.get("user_id")).equals ((Integer) o.get("id"))){
                            m.put("username",o.get("username"));
                            m.put("userPhoto",o.get("photo"));
                            m.put("sex",o.get("sex"));
                        }
                    });
                });

                return maps;
            }


        }catch (Exception e){
            e.printStackTrace();
        }
        return null;

    }

    public List<Map<String, Object>> findFocusByTel(String tel, int pageNum, int pageSize) {
        return null;
    }

    public List<Map<String, Object>> search(String content, int pageNum, int pageSize) {
        String sql = "select aa.user_id userId,aa.content, bb.photo,aa.distance,date_format(aa.create_time, '%Y-%m-%d %H:%i:%s') createTime from (" +
                "SELECT * FROM (SELECT *, TRUNCATE (st_distance_sphere (point (116.4, 39.9),point (aaa.publish_location_longitude,aaa.publish_location_latitude " +
                ")),2) AS distance FROM (select * from sys_user_want_to where content like ? ORDER BY create_time desc) aaa ORDER By distance) a WHERE " +
                " a.distance <= 20000) aa,sys_content_photo bb where aa.id = bb.want_to_id ORDER BY aa.create_time DESC,aa.distance DESC";

        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, "%"+content+"%");
        if(maps != null && maps.size()>0){
            StringBuffer id = new StringBuffer();
            id.append("(");
            maps.forEach(m->{
                id.append(m.get("userId"));
                id.append(",");
            });

            String ids = id.substring(0,id.length()-1);
            ids+=")";

            String sql2 = "select a.id,a.username,a.sex,b.photo from sys_user a, sys_user_photo b where a.photo_id = b.id and a.id in "+ ids;
            List<Map<String, Object>> maps1 = jdbcTemplate.queryForList(sql2);


            if(maps1 != null && maps1.size()>0){
                maps.forEach(m->{
                    maps1.forEach(o->{
                        if(((Integer)m.get("userId")).equals((Integer) o.get("id"))){
                            m.put("username",o.get("username"));
                            m.put("userPhoto",o.get("photo"));
                            m.put("sex",o.get("sex"));
                        }
                    });
                });
            }
        }



        return maps;
    }

    public Map<String, Object> findById(int id) {
        String sql = "select b.id userId,a.id, a.content,DATE_FORMAT(a.create_time,'%Y-%m-%d %H:%i:%s') createTime,b.username,b.sex,c.photo userPhoto,d.photo " +
                " from sys_user_want_to a,sys_user b, sys_user_photo c,sys_content_photo d where a.id = ? and a.user_id = b.id and " +
                " b.photo_id = c.id and a.id = d.want_to_id";

        try {
            return jdbcTemplate.queryForMap(sql,id);
        }catch (Exception e){
            return null;
        }
    }

    public int messageSave(String mes) {
        JSONObject jsonObject = JSON.parseObject(mes);
        String tel = (String) jsonObject.get("tel");
        String sql = "select id from sys_user where phone_number = ?";
        Integer contentId = (Integer) jsonObject.get("contentId");
        String message = jsonObject.getString("message");
        try {
            Map<String, Object> map = jdbcTemplate.queryForMap(sql, tel);
            Integer userId = (Integer) map.get("id");
            String sql1 = "insert into sys_content_message (user_id,content_id,message) values (?,?,?)";
            return jdbcTemplate.update(sql1,userId,contentId,message);
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }


    }

    public  List<Map<String, Object>> findByContentId(int contentId) {
        String sql = "select b.id userId,b.username,b.sex,a.message,c.photo,a.good,DATE_FORMAT(a.create_time,'%Y-%m-%d %H:%i:%s') createTime from sys_content_message a,sys_user b,sys_user_photo c " +
                "where a.content_id = ? and a.user_id = b.id and b.photo_id = c.id order by a.create_time desc";
        return jdbcTemplate.queryForList(sql,contentId);
    }

    public int say(String mes) {
        JSONObject jsonObject = JSON.parseObject(mes);
        String tel = (String) jsonObject.get("tel");
        String sql = "select id from sys_user where phone_number = ?";
        String image = (String) jsonObject.get("image");
        String message = jsonObject.getString("message");
        try{
            Map<String, Object> map1 = jdbcTemplate.queryForMap(sql, tel);
            Integer userId = (Integer) map1.get("id");
            Random random = new Random();
            int i = random.nextInt(3179)+1;

            //获取地址
            String locationSql = "select * from bd_location where id = ?";
            Map<String, Object> map = jdbcTemplate.queryForMap(locationSql, i);
            //获取一个随机发布信息 将地址替换掉就行了 不用生成的
            Map<String, Object> travelInfo = TravelInfoGenerator.generateRandomTravelInfo();
            String createWantTo = "insert into sys_user_want_to (user_id,location_id,start_time,end_time,days,content,publish_location_longitude,publish_location_latitude) values (?,?,?,?,?,?,?,?)";

            Random random2 = new Random();
            double publishLocationId = random.nextDouble()*59+75.0;
            Random random3 = new Random();
            double publishLocationId2 = random.nextDouble()*35+18.0;
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(createWantTo, new String[] { "id" }); // 指定自动生成的字段
                ps.setInt(1, userId);
                ps.setInt(2, i);
                ps.setObject(3, travelInfo.get("startTime"));
                ps.setObject(4, travelInfo.get("endTime"));
                ps.setObject(5, travelInfo.get("days"));
                ps.setObject(6, message);
                ps.setDouble(7, publishLocationId);
                ps.setDouble(8, publishLocationId2);

                return ps;
            }, keyHolder);

            // 获取自动生成的 ID
            long generatedId = keyHolder.getKey().longValue();
            String photoSql = "insert into sys_content_photo (photo,want_to_id) values (?,?)";

            jdbcTemplate.update(photoSql, image,generatedId);

        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

    public int good(int id, int good) {
        String sql = "update sys_user_want_to set good = ? where id = ?";
        return jdbcTemplate.update(sql,good,id);
    }
}
